﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using DTO_QLNS;
using System.Collections;
using System.Data;

namespace ADO_QLNS
{
    public class BookADO : QLNS_ADO
    {
        BookDTO _BookDTO;

        public BookDTO GetBook(BookDTO dto)
        {
            _Helper.Connect();
            {
                string sql = "Select b.*, a.AuthorName, g.GenreName, p.PublisherName From Book b, Author a, Genre g, Publisher p";
                sql += "Where b.AuthorID=a.AuthorID AND b.GenreID=g.GenreID AND p.PublisherID=b.PublisherID AND b.BookID = {0}";
                sql = String.Format(sql, dto.BookID);
                _BookDTO = (BookDTO)_Helper.exeQuery(sql);
            }
            _Helper.Disconnect();
            return _BookDTO;
        }

        public ArrayList GetBookByAuthor(int authorID)
        {
            _Helper.Connect();
            ArrayList arr;
            {
                string sql = "Select b.*, a.AuthorName, g.GenreName, p.PublisherName From Book b, Author a, Genre g, Publisher p";
                sql += "Where b.AuthorID=a.AuthorID AND b.GenreID=g.GenreID AND p.PublisherID=b.PublisherID AND b.AuthorID = {0}";
                sql = String.Format(sql, authorID);
                _Helper.DataProvider.CreateAdapter(sql);
                DataSet ds = new DataSet();
                _Helper.DataProvider.Adapter.Fill(ds);

                arr = ConvertDataSetToArrayList(ds);
            }
            _Helper.Disconnect();
            return arr;
        }

        public override string InsertString(TransferObject dto)
        {
            _BookDTO = (BookDTO)dto;
            string insertCmd = "INSERT INTO Book(GenreID,PublisherID,BookName,CurrentPrice,Image,AuthorID) VALUES({0},{1},'{2}',{3},'{4}',{5},'{6}')";
            return String.Format(insertCmd, _BookDTO.Category.GenreID, _BookDTO.Publisher.PublisherID, _BookDTO.BookName, _BookDTO.CurrentPrice, _BookDTO.ImagePath, _BookDTO.Author.AuthorID, _BookDTO.Content);
        }

        public override string UpdateString(TransferObject dto)
        {
            _BookDTO = (BookDTO)dto;
            string updateCmd = "UPDATE Book SET GenreID={0},PublisherID={1},BookName='{2}',CurrentPrice={3},Image='{4}',AuthorID={5},Content='{6}' WHERE BookID={6}";
            return String.Format(updateCmd, _BookDTO.Category.GenreID, _BookDTO.Publisher.PublisherID, _BookDTO.BookName, _BookDTO.CurrentPrice, _BookDTO.ImagePath, _BookDTO.Author.AuthorID, _BookDTO.Content,_BookDTO.BookID);
        }

        public override string DeleteString(TransferObject dto)
        {
            _BookDTO = (BookDTO)dto;
            string deleteCmd = "DELETE FROM Book WHERE BookID={0}";
            return String.Format(deleteCmd, _BookDTO.BookID);
        }

        protected override object GetDataFromDataRow(System.Data.DataTable dt, int i)
        {
            BookDTO dto = new BookDTO();
            dto.BookID = int.Parse(dt.Rows[i]["BookID"].ToString());
            dto.BookName = dt.Rows[i]["BookName"].ToString();
            dto.Author.AuthorID = int.Parse(dt.Rows[i]["AuthorID"].ToString());
            dto.Author.AuthorName = dt.Rows[i]["AuthorName"].ToString();
            dto.Category.GenreID = int.Parse(dt.Rows[i]["GenreID"].ToString());
            dto.Category.GenreName = dt.Rows[i]["GenreName"].ToString();
            dto.Publisher.PublisherID = int.Parse(dt.Rows[i]["PublisherID"].ToString());
            dto.Publisher.PublishName = dt.Rows[i]["PublisherName"].ToString();
            dto.CurrentPrice = double.Parse(dt.Rows[i]["CurrentPrice"].ToString());
            dto.Content = dt.Rows[i]["Content"].ToString();
            dto.ImagePath = dt.Rows[i]["Image"].ToString();

            return dto;
        }

        public ArrayList GetBookByCategory(int categoryID)
        {
            _Helper.Connect();
            ArrayList arr;
            {
                string sql = "SELECT b.*, a.AuthorName, g.GenreName, p.PublisherName FROM Book b, Author a, Genre g, Publisher p WHERE b.AuthorID=a.AuthorID AND b.GenreID=g.GenreID AND p.PublisherID=b.PublisherID AND b.GenreID = {0}";
                sql = String.Format(sql, categoryID);
                _Helper.DataProvider.CreateAdapter(sql);
                DataSet ds = new DataSet();
                _Helper.DataProvider.Adapter.Fill(ds);

                arr = ConvertDataSetToArrayList(ds);
            }
            _Helper.Disconnect();
            return arr;
        }

        public ArrayList SearchByAuthorAndCategory(string name, int cat, int aut)
        {
            _Helper.Connect();
            ArrayList arr;
            {
                string cond1 = "b.GenreID=" + cat;
                string cond2 = "b.AuthorID=" + aut;
                string cond3 = String.Format("LOWER(BookName) LIKE LOWER(N'%{0}%')", name);
                string AND = " AND ";

                string sql = "SELECT b.*, a.AuthorName, g.GenreName, p.PublisherName FROM Book b, Author a, Genre g, Publisher p WHERE b.AuthorID=a.AuthorID AND b.GenreID=g.GenreID AND p.PublisherID=b.PublisherID";
                if (cat > -1)
                {
                    sql +=  AND + cond1;
                }
                if (aut > -1)
                {
                    sql += AND + cond2;
                }
                if (name != "")
                {
                    sql += AND + cond3;
                }

                _Helper.DataProvider.CreateAdapter(sql);
                DataSet ds = new DataSet();
                _Helper.DataProvider.Adapter.Fill(ds);

                arr = ConvertDataSetToArrayList(ds);
            }
            _Helper.Disconnect();
            return arr;
        }
    }
}
